/*
SELECT * FROM fn_bom_parts_for_bomReport ( '1197' )
SELECT * FROM fn_bom_parts_for_bomReport ( '2130' )
*/

ALTER  FUNCTION fn_bom_parts_for_bomReport( @assemblyProcessStepId VARCHAR(50) ) returns @results_table TABLE (bomPartNumber VARCHAR(50), qtyReqPerUnit INT, qtyAvailable INT)
AS

BEGIN
DECLARE @bomProcessStepId BIGINT
DECLARE @qty INT
DECLARE @bomPartNumber VARCHAR(50)
DECLARE @lotId BIGINT
DECLARE @qtyBal INT

DECLARE @results TABLE ( bomPartNumber VARCHAR(50), qtyReqPerUnit INT, qtyAvailable INT )

DECLARE processStepIdAndQty CURSOR FOR SELECT bom_processStep_id, qty FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @assemblyProcessStepId
OPEN processStepIdAndQty

FETCH NEXT FROM processStepIdAndQty INTO @bomProcessStepId, @qty
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @bomPartNumber = part_number FROM PRODUCT WHERE PROCESSSTEP_ID = @bomProcessStepId
	SET @qtyBal = 0
	SELECT @qtyBal =  SUM( ISNULL( IL_qty_balance, IL_qty_IN ) ) FROM Inventory_lot WHERE lot_id IN ( SELECT lot_id FROM LOT_TABLE WHERE processStep_id = @bomProcessStepId and lot_current_status = 'Running' ) 

	INSERT INTO @results (bomPartNumber,qtyReqPerUnit,qtyAvailable ) VALUES ( @bomPartNumber, @qty, ISNULL(@qtyBal,0) )

	SET @qtyBal = 0
	FETCH NEXT FROM processStepIdAndQty INTO @bomProcessStepId, @qty
END

CLOSE processStepIdAndQty
INSERT @results_table SELECT * FROM @results

RETURN 

END